import json
import os
import time

import pandas as pd
import psutil
import redis
from multiprocessing import Process, Value
from flask import request, g, send_file
from sqlalchemy import text, asc, desc
from backend.extensions import db
from .models import OPCUAPoint, OPCUADevice, Alarm
from backend.helpers import success_response, error_response
from . import opcua_app
from backend.adapter import OPCUAAdapter
from backend.connectors.mysql import engine as mysql_engine

process_dict = {}  # 记录进程名字到状态位的映射
process_list = []  # 记录进程对象


def background_task(opcua_info):
    """
    后台进程实现数据采集
    :param opcua_info:
    :return:
    """
    opcua_adapter_obj = OPCUAAdapter(opcua_info)
    opcua_adapter_obj.run()


@opcua_app.route('/task/start', methods=['POST'])
def start_process():
    """
    启动任务
    :return:
    """
    try:
        opcua_info = None
        device_id = request.json.get('device_id')
        global process_dict, process_list
        redis_conn: redis.Redis = g.redis_client
        opcua_info_list = redis_conn.lrange('opcua_info', 0, -1)
        for opcua_info in opcua_info_list:
            opcua_info_obj = json.loads(opcua_info)
            if opcua_info_obj['id'] == device_id:
                opcua_info = opcua_info_obj
                break
        if device_id not in process_dict:
            # process_dict[device_id] = Value('b', True)
            redis_conn.set(f'task_{device_id}_flag', 1)
            # process = Process(target=background_task, args=(opcua_info, process_dict[device_id]), name=f'task_{device_id}')
            process = Process(target=background_task, args=(opcua_info,), name=f'task_{device_id}', daemon=True)
            process.start()
            process_list.append(process)
            process_dict[device_id] = process.pid
            return success_response(message=f'started {process.pid}', code=200, data=process.pid)
        else:
            return error_response(message="already running or start error", code=500)
    except Exception as e:
        return error_response(message=str(e), code=500)


@opcua_app.route('/task/stop', methods=['POST'])
def stop_process():
    """
    停止任务
    :return:
    """
    try:
        global process_dict, process_list
        redis_conn: redis.Redis = g.redis_client
        device_id = request.json.get('device_id')
        _run_pid = request.json.get('pid')
        force_shutdown = request.json.get('force') or False
        current_process_obj = None
        for process in process_list:
            if process.name == f'task_{device_id}':
                current_process_obj = process
        if device_id in process_dict:
            # process_dict[device_id].value = False
            if current_process_obj:
                if not force_shutdown:
                    redis_conn.set(f'task_{device_id}_flag', 0)
                    time.sleep(2) # 等2s
                current_process_obj.terminate()  # 销毁进程
                current_process_obj.join()  # 等待进程结束
                process_list.remove(current_process_obj)
            del process_dict[device_id]
            return success_response(message="stopped", code=200)
        else:
            return error_response(message="not running", code=400)
    except Exception as e:
        return error_response(message=str(e), code=500)


# http://127.0.0.1:5000/download?filename=example.txt
@opcua_app.route('/download', methods=['GET'])
def download_device_template():
    filename = request.args.get('filename')
    if not filename:
        return error_response(message="Filename not provided", code=400)
    file_path = f"./files/{filename}"
    try:
        return send_file(file_path, as_attachment=True)
    except Exception as e:
        return error_response(message=f"File not found+{str(e)}", code=404)


@opcua_app.route('/upload', methods=['POST'])
def upload_file():
    # 检查是否有上传文件
    if 'file' not in request.files:
        return error_response(message="No file part", code=400)

    file = request.files['file']
    # 如果没有选择文件，返回错误
    if file.filename == '' and (file.filename.startswith('opcua_device') or file.filename.startswith('opcua_point')):
        return error_response(message='未上传文件或者文件格式不正确')

    if file and allowed_file(file.filename):
        # 保存上传的文件到服务器的临时目录
        try:
            if file.filename.startswith('opcua_device'):
                file_path = os.path.join(r'./uploads', 'opcua_device_info.xlsx')
                file.save(file_path)
            elif file.filename.startswith('opcua_point'):
                file_path = os.path.join(r'./uploads', 'opcua_point_info.xlsx')
                file.save(file_path)
            return success_response(message='上传成功', code=200)
        except Exception as e:
            return error_response(message=f"{str(e)}")
    else:
        return error_response(message="Invalid file type")


def allowed_file(filename):
    # 检查文件后缀名
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ['xls', 'xlsx']


@opcua_app.route('/import_device_data', methods=['POST'])
def import_device_data():
    # 获取uploads文件夹中的以opcua_device_开头的文件
    file_path = './uploads/opcua_device_info.xlsx'
    try:
        # engine = create_engine(url=current_app.config.get('SQLALCHEMY_DATABASE_URI'))

        df = pd.read_excel(file_path, usecols=['device_name', 'url'])
        # Write the DataFrame to SQL
        df_dict = df.to_dict(orient='records')

        for row in df_dict:
            obj = OPCUADevice(**row)
            obj.save()
        # df.to_sql('opcuadevices', con=engine, if_exists='append', index=False)
        return success_response(message='导入成功!')
    except Exception as e:
        return error_response(message=str(e))


@opcua_app.route('/import_point_data', methods=['POST'])
def import_point_data():
    # 获取uploads文件夹中的以opcua_device_开头的文件
    file_path = './uploads/opcua_point_info.xlsx'
    try:
        # engine = create_engine(url=current_app.config.get('SQLALCHEMY_DATABASE_URI'))

        df = pd.read_excel(file_path, usecols=['tag_uuid',
                                               'node_id',
                                               'interval',
                                               'active',
                                               'active_alarm',
                                               'alarm_up',
                                               'alarm_down',
                                               'alarm_up_info',
                                               'alarm_down_info',
                                               'alarm_up_change',
                                               'alarm_down_change',
                                               'active_archive',
                                               'archive_onchange',
                                               'archive_interval',
                                               'active_scale',
                                               'scale_sign',
                                               'scale_factor',
                                               'mqtt_topic_name',
                                               'unit',
                                               'comments',
                                               'device_id'])
        # Write the DataFrame to SQL
        df_dict = df.to_dict(orient='records')
        for row in df_dict:
            obj = OPCUAPoint(**row)
            obj.save()
        # df.to_sql('opcuadevices', con=engine, if_exists='append', index=False)
        return success_response(message='导入成功!')
    except Exception as e:
        return error_response(message=str(e))


@opcua_app.route('/deploy', methods=['POST'])
def deploy():
    """
    将导入的设备和点表导入到redis中
    :return:
    """
    try:
        redis_conn: redis.Redis = g.redis_client
        redis_conn.ltrim('opcua_info', 1, 0)
        opcua_devices = OPCUADevice.get_all()
        for device in opcua_devices:
            points = OPCUAPoint.filter(device_id=device.id)
            device_info_dict = device.to_dict()
            device_info_dict.setdefault('opcua_points', [point.to_dict() for point in points])
            redis_conn.lpush('opcua_info', json.dumps(device_info_dict))
        return success_response(message='ok')
    except Exception as e:
        return error_response(message=str(e), code=400)


@opcua_app.route('/delete_all_data', methods=['POST'])
def delete_all_data():
    """
    清空所有数据
    :return:
    """
    table_name = request.json.get('table_name')
    if not table_name:
        return error_response(message='not table_name', code=400)
    try:
        # 开始事务
        with db.session.begin():
            db.session.execute(text("SET FOREIGN_KEY_CHECKS=0;"))
            db.session.execute(text(f"TRUNCATE TABLE {table_name}"))
            db.session.execute(text("SET FOREIGN_KEY_CHECKS=1;"))
        # 提交事务
        db.session.commit()
        return success_response(message=f'{table_name} 数据清空成功!')
    except Exception as e:
        db.session.rollback()
        return error_response(message=e, code=500)


@opcua_app.route('/get_deploy_data', methods=['POST'])
def get_deploy_data():
    """
    获取redis中数据
    :return:
    """
    try:
        redis_conn: redis.Redis = g.redis_client
        opcua_info = redis_conn.lrange('opcua_info', 0, -1)
        if not opcua_info or len(opcua_info) == 0:
            return error_response(message='请先导入点表和设备链接信息后再执行Run!', code=400)
        result = []
        for _opcua_info in opcua_info:
            result.append(json.loads(_opcua_info))
        return success_response(data=result)
    except Exception as e:
        return error_response(message=str(e))


@opcua_app.route('/get_monitor_data', methods=['POST'])
def get_monitor_data():
    try:
        redis_conn: redis.Redis = g.redis_client
        pid_info = request.json.get('pid_info')
        pid_status = {}
        for device_id, engine_pid in pid_info.items():
            if psutil.pid_exists(engine_pid):
                pid_status[device_id] = True
            else:
                pid_status[device_id] = False
            monitor_info = redis_conn.hgetall(f'performance_{engine_pid}')
            pid_status[f'{engine_pid}'] = monitor_info
        return success_response(data=pid_status)
    except Exception as e:
        return error_response(message=str(e))


@opcua_app.route('/get_opcua_values', methods=['POST'])
def get_opcua_values():
    try:
        redis_conn: redis.Redis = g.redis_client
        data = redis_conn.hgetall('opcua_addr')
        return success_response(data=data)
    except Exception as e:
        return error_response(message=str(e))


@opcua_app.route('/get_alarms',defaults={'id': None}, methods=['GET', 'POST', 'DELETE'])
def get_alarms(id):
    try:
        if request.method == 'GET':
            if id:
                # 查询单条记录
                item = Alarm.query.filter_by(id=id, is_deleted=False).first()
                if item:
                    _item = item.to_dict()
                    return success_response(data=_item)
                else:
                    return error_response(message=f'not found', code=404)
            else:
                # 查询所有记录
                page = request.args.get('page', 1, type=int)
                per_page = request.args.get('per_page', 5, type=int)
                sort_by = request.args.get('sort_by', 'id')
                order = request.args.get('order', 'desc')
                search = request.args.get('search', '')

                query = Alarm.query

                if search:
                    query = query.filter(Alarm.alarm_message.contains(search))

                if order == 'asc':
                    query = query.order_by(asc(Alarm.id))
                else:
                    query = query.order_by(desc(Alarm.id))

                items = query.paginate(page=page, per_page=per_page, count=True)

                total_page = items.total // items.per_page
                total_page += 1
                result = []
                for item in items.items:
                    if item:
                        _item = item.to_dict()
                        result.append(_item)
                content = {
                    'total': items.total,
                    'page': items.page,
                    'total_page': total_page,
                    'per_page': items.per_page,
                    'items': result,
                }
                return success_response(data=content)
        elif request.method == 'POST':
            pass
        elif request.method == 'DELETE':
            pass
    except Exception as e:
        return error_response(message=str(e))


@opcua_app.route('/get_archives', methods=['POST'])
def get_archives():
    pass


@opcua_app.route('/download_table_data', methods=['POST'])
def download_table_data_to_excel():
    """
    下载所有的数据到excel
    :return:
    """
    try:
        table = request.json.get("table")
        if table == 'device':
            sql = "SELECT * FROM opcuadevices;"
            df = pd.read_sql(sql, mysql_engine)
            df.to_excel('./downloads/opcua_device_data.xlsx')
            return send_file('./downloads/opcua_device_data.xlsx', as_attachment=True)
        elif table == 'point':
            sql = "SELECT * FROM opcuapoints;"
            df = pd.read_sql(sql, mysql_engine)
            df.to_excel('./downloads/opcua_point_data.xlsx')
            return send_file('./downloads/opcua_point_data.xlsx', as_attachment=True)
    except Exception as e:
        return error_response(message=str(e))


@opcua_app.route('/logs', methods=['GET'])
def get_logs():
    log_file_path = './logs/opcua_adapter_logger.txt'  # 假设日志文件存放在应用的根目录下
    log_lines = []

    if os.path.exists(log_file_path):
        with open(log_file_path, 'r', encoding='utf8') as file:
            log_lines = file.readlines()
        # 将最新的日志显示在最前
        log_lines.reverse()

        # 返回 JSON 格式的日志数据
    return success_response(data=log_lines)
